"mr_gofast" (jake_berenshteyn)
05/08/2014 at 09:41 • Filed to: None
0
17|
"mr_gofast" (jake_berenshteyn)
05/08/2014 at 09:41 • Filed to: None | 0
| 17 |
i have two worksheets in one document
i need to have cells from one worksheet populate with text data from specific cells in the second sheet
example being
sheet one has a 2 columns - Code and code description
sheet 2 has code and code description but the descriptions are missing
i need the missing desciptions in sheet 2 to be populated from sheet one and i also need them to populate to match the individual codes, so all code 10.6.12 in the entire document get the same description, all code 14.3 across the doc get the same description etc..
can anyone tel me how? can it be done to begin with?
Twingo Tamer - About to descend into project car hell.
> mr_gofast
05/08/2014 at 09:43 |
|
jvirgs drives a Subaru
> mr_gofast
05/08/2014 at 09:45 |
|
I believe an IF function would do the trick. They can get kind of complicated. Best bet is to use the GOOGLE machine and look up how to do them.
Yowen - not necessarily not spaghetti and meatballs
> mr_gofast
05/08/2014 at 09:46 |
|
Yep, you can refer to cells on different sheets to populate the sheet in question.
crowmolly
> mr_gofast
05/08/2014 at 09:47 |
|
Like this?
=sheetname!cellnumber is the formula.
Driver_41
> mr_gofast
05/08/2014 at 09:49 |
|
If I'm understanding what you have, I think you'll want to use the vlookup function. Microsoft's own help site is pretty good actually: http://office.microsoft.com/en-us/excel-he…
Sn210
> mr_gofast
05/08/2014 at 09:51 |
|
I think you want vlookup function... If you do =vlookup(
Then the cell with the data you want to match, I think for you would be the code
,
Then highlight the columns starting with the code and go all the way to the column with the description
,
Then enter the column the data you want to pull is in, so if the descriptions are in the third column, enter 3
,
Then "false" for an exact match
)
Example: =vlookup(c2,sheetaA1:A3,3, false)
Make sure the data types match, so if your codes are "number" on a they should also be "number" on sheet b. It should take all the descriptions on sheet a and pair them up with the codes with missing descriptions on sheet b.
It's kind of hard to explain over Kinja, but I think vlookup is what you want. Good luck!
Nibbles
> mr_gofast
05/08/2014 at 09:54 |
|
If the row position in sheet 1 matches sheet 2, you could do this: on Sheet 2, select the cell that you want populated with data from Sheet 1. In the formula bar, type "=[='[sheetname]'![cellnum]" (without quotes) where [sheetname] is the sheet name (single quotes if there is a space in the name] and [cellnum] is the cell location, eg B12.
If the data isn't in the same location across sheets, then you may need to use the VLOOKUP function. You'll probably need to Google how to set that up right because I'm not sure I remember exactly how.
|
Nibbles
> Sn210
05/08/2014 at 09:55 |
|
Nice! I was attempting to remember how to explain VLOOKUP and you got it right on the nose ;)
|
Sn210
> Nibbles
05/08/2014 at 09:57 |
|
Haha thanks! I've been all about the vlookup at work since I learned about it a few months ago. We are updating a massive amount of data in one our systems and vlookup has made my life so much easier
|
mr_gofast
> Sn210
05/08/2014 at 10:02 |
|
so in the formula c2 is what though? thats the only thing im confused on. is C2 the empty spot wherre i want the description to populate?
|
Sn210
> mr_gofast
05/08/2014 at 10:05 |
|
no c2 is what you want to match up. So it should be the code on sheet 2. You want to look up that data on sheet one, then pull the related description over to sheet 2
|
mr_gofast
> Sn210
05/08/2014 at 10:12 |
|
damn not working..im sure i screwed up..
worksheet 1 = Defect codes in 2 columns ; A is the codes running down, B is the matching descriptions to them
worksheet 2 = the one with the blank descriptions; Column U has the Codes while column AH is the blank one i need to populate.
my formula is on AH2 and ive got =vlookup( and im not sure what goes next - :(
|
Sn210
> mr_gofast
05/08/2014 at 10:15 |
|
U from sheet 2! So (U,highlight sheet 1 columns a to b,2,false)
|
mr_gofast
> Sn210
05/08/2014 at 10:18 |
|
awesome thanks so much! and i can just drag teh cursor down the whole way and it should fill?
|
Sn210
> mr_gofast
05/08/2014 at 10:20 |
|
that's right! Vlookup rules!
|
mr_gofast
> Sn210
05/08/2014 at 11:13 |
|
if i want to do the entire column at once and not press f4 and down arrow each time how do i do it?
|
Sn210
> mr_gofast
05/08/2014 at 11:18 |
|
you should be able to click and drag from the black square in the bottom right corner of the highlighted cell. If you double click on that same small black square, I think it carries the formula all the way to the bottom of the sheet